package dao;

import model.Role;
import utils.JDBCTools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 角色dao
 */
public class RoleDao {

    /**
     * 增
     * @return rows 影响行数
     */
    public int insert(Role role){

        Connection con=null;
        PreparedStatement pstm=null;
        String sql="insert into role_talble values(null,?,?,?,?,?,?,?,?,?) ";

        int rows=0;
        try {
            con= JDBCTools.getCon();
            pstm=con.prepareStatement(sql);

            pstm.setString(1,role.getRlName());
            pstm.setString(2,role.rlRemark);
            pstm.setString(3,role.getRlWhether());
            pstm.setInt(4,role.getRlSort());
            pstm.setDate(5,role.getRlDate());
            pstm.setString(6,role.getRlCjName());
            pstm.setString(7,role.getRlRlStandbyOne());
            pstm.setString(8,role.getRlRlStandbyTwo());
            pstm.setString(9,role.getRlRlStandbyThree());

            rows=pstm.executeUpdate();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.close(con,pstm);
        }
        return rows;
    }


    /**
     * 删除
     * @return rows 影响行数
     */
    public int delete(int rlId){
        Connection con = null;
        PreparedStatement pstm = null;

        String sql = "delete from role_talble where rl_id = ?";
        int rows = 0;
        try {
            con = JDBCTools.getCon();
            pstm = con.prepareStatement(sql);

            pstm.setInt(1,rlId);
            rows = pstm.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.close(con,pstm);
        }
        return rows;
    }


    /**
     * 修改
     * @return rows 影响行数
     */
    public int update(Role role){
        Connection con = null;
        PreparedStatement pstm = null;

        String sql = "update role_talble set rl_name=?,rl_remark=?,rl_whether=?,rl_sort=? rl_date=?,rl_cjname=?,rl_standby_one=?,rl_standby_two=?,rl_standby_three=? where rl_id = ?";
        int rows = 0;
        try {
            con = JDBCTools.getCon();
            pstm = con.prepareStatement(sql);

            pstm.setString(1,role.getRlName());
            pstm.setString(2,role.getRlRemark());
            pstm.setString(3,role.getRlWhether());
            pstm.setInt(4,role.getRlSort());
            pstm.setDate(5,role.getRlDate());
            pstm.setString(6,role.getRlCjName());
            pstm.setString(7,role.getRlRlStandbyOne());
            pstm.setString(8,role.getRlRlStandbyTwo());
            pstm.setString(9,role.getRlRlStandbyThree());
            pstm.setInt(10,role.getRlId());

            rows = pstm.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.close(con,pstm);
        }
        return rows;
    }


    /**
     * 查询全部
     * @return
     */
    public List<Role> selectAll(Integer rlId){
        Connection con = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        String sql = "select * from role_talble where rl_id=?";
        List<Role> roleList = new ArrayList<>();
        try {
            con = JDBCTools.getCon();
            pstm = con.prepareStatement(sql);
            pstm.setInt(1,rlId);
            rs = pstm.executeQuery();
            while (rs.next()){
                Role role=new Role();
                role.setRlId(rs.getInt(1));
                role.setRlName(rs.getString(2));
                role.setRlRemark(rs.getString(3));
                role.setRlWhether(rs.getString(4));
                role.setRlSort(rs.getInt(5));
                role.setRlDate(rs.getDate(6));
                role.setRlCjName(rs.getString(7));
                role.setRlRlStandbyOne(rs.getString(8));
                role.setRlRlStandbyTwo(rs.getString(9));
                role.setRlRlStandbyThree(rs.getString(10));

                roleList.add(role);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.close(con,pstm,rs);
        }
        return roleList;
    }


    /**
     * 根据ID查询
     * @return
     */
    public Role selectId(Integer rlId){
        Connection con = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        String sql = "select * from role_talble where rl_id=?";
        List<Role> roleList = new ArrayList<>();
        try {
            con = JDBCTools.getCon();
            pstm = con.prepareStatement(sql);
            pstm.setInt(1,rlId);
            rs = pstm.executeQuery();
            while (rs.next()){
                Role role=new Role();
                role.setRlId(rs.getInt(1));
                role.setRlName(rs.getString(2));
                role.setRlRemark(rs.getString(3));
                role.setRlWhether(rs.getString(4));
                role.setRlSort(rs.getInt(5));
                role.setRlDate(rs.getDate(6));
                role.setRlCjName(rs.getString(7));
                role.setRlRlStandbyOne(rs.getString(8));
                role.setRlRlStandbyTwo(rs.getString(9));
                role.setRlRlStandbyThree(rs.getString(10));

                return role;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.close(con,pstm,rs);
        }
        return null;
    }

}
